package com.hellojavaweb.dao;

import com.hellojavaweb.bean.Money;
import com.hellojavaweb.bean.User;
import com.sun.org.apache.bcel.internal.generic.RETURN;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by daitiantian on 2017/1/12.
 */
public class UserDao {

    String dbDriver = "com.mysql.jdbc.Driver";//数据库驱动
    String dbAccount = "root";//数据库账号
    String dbPassword = "daitt";//数据库密码
    String dbIp = "localhost";//数据库地址
    String dbPort = "3306";//默认数据库端口
    String dbSchema = "test";//数据库名
    String url = "jdbc:mysql://" + dbIp + ":" + dbPort + "/" + dbSchema;
    Connection connection = null;//数据库连接器
    PreparedStatement statement = null;
    ResultSet rSet = null;

    public UserDao() {
    }

    public void conDataBase()

    {
        //建立连接
        try {
            Class.forName(dbDriver);
            connection = DriverManager.getConnection(url,dbAccount,dbPassword);
        } catch (Exception e) {
            e.printStackTrace();
            connection = null;
        }

    }


    public void closeDataBase() throws SQLException

    {
        //操作完后关闭数据库连接
        try {
            if(statement!=null) {
                statement.close();
            }
            if(connection!=null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public String selDataBase(String username) throws SQLException {

        conDataBase();
        //查询数据库
        String sql = "select pass from `t_user_info` where `username`= ? ";
        statement = connection.prepareStatement(sql);
        statement.setString(1, username);//设置第一个参数的值
        // statement.setString(1, "xx");//设置第一个参数的值
        rSet = statement.executeQuery();//执行查询，结果放在rSet中
        //循环读取结果
        String dbPasswd = null;
        if (rSet.next()) {
            dbPasswd = rSet.getString(1);
        }else{
            dbPasswd = null;
        }
        closeDataBase();

        return dbPasswd;
    }

    public boolean sleDataBase(String username) throws SQLException {

        conDataBase();

        //查询数据库
        String sql = "select * from `t_user_info` where `username`= ? ";
        statement = connection.prepareStatement(sql);
        statement.setString(1, username);//设置第一个参数的值
        // statement.setString(1, "xx");//设置第一个参数的值
        rSet = statement.executeQuery();//执行查询，结果放在rSet中
        //循环读取结果
        boolean bl;
        if (rSet.next()) {
            bl = false;
        }else{
            bl = true;
        }

        closeDataBase();
        return bl;
    }

    public int insDataBase(String username,String pass) throws SQLException {
        conDataBase();

        String sql = "INSERT into `t_user_info` (`username`,`pass`)  VALUES (?,?)";
        statement = connection.prepareStatement(sql);
        statement.setString(1, username);//设置第一个参数的值
        statement.setString(2, pass);//设置第二个参数的值

        int i;
        i = statement.executeUpdate();

        closeDataBase();
        return i;
    }

    public List<User> slaDataBase() throws SQLException {

        conDataBase();

        //查询数据库
        List<User>  userList = new ArrayList<User>();

        String sql = "select * from `t_user_info`";
        statement = connection.prepareStatement(sql);
        rSet = statement.executeQuery();//执行查询，结果放在rSet中
        //循环读取结果
        while (rSet.next()) {
            User user = new User();
            user.setNum(rSet.getInt(1));
            user.setUsername(rSet.getString(2));
            user.setPass(rSet.getString(3));
            userList.add(user);
        }
        closeDataBase();
        return userList;
    }
    public List<Money> sla2DataBase() throws SQLException {

        conDataBase();

        //查询数据库
        List<Money>  userList = new ArrayList<Money>();

        String sql = "select `t_money`.`id`,`t_money`.`int_id`,`t_user_info`.`username`,`t_money`.`r_money` from `t_user_info`,`t_money` where `t_user_info`.`int_id` = `t_money`.`int_id`  ";
        statement = connection.prepareStatement(sql);

        rSet = statement.executeQuery();//执行查询，结果放在rSet中
        //循环读取结果
        while (rSet.next()) {
            Money money = new Money();
            money.setOrder(rSet.getInt(1));
            money.setNum(rSet.getInt(2));
            money.setUsername(rSet.getString(3));
            money.setMoney(rSet.getDouble(4));
            userList.add(money);
        }
        closeDataBase();
        return userList;
    }
    public Money slmDataBase(String username) throws SQLException {

        conDataBase();

        //查询数据库
        Money  userMoney = new Money();

        String sql = "select `t_money`.`id`,`t_money`.`int_id`,`t_user_info`.`username`,`t_money`.`r_money` from `t_user_info`,`t_money` where `t_user_info`.`int_id` = `t_money`.`int_id` and  `t_user_info`.`username`=?";
        statement = connection.prepareStatement(sql);
        statement.setString(1, username);//设置第一个参数的值
        rSet = statement.executeQuery();//执行查询，结果放在rSet中
        //循环读取结果
        if(rSet.next()) {
            userMoney.setOrder(rSet.getInt(1));
            userMoney.setNum(rSet.getInt(2));
            userMoney.setUsername(rSet.getString(3));
            userMoney.setMoney(rSet.getDouble(4));
        }
        else{
            userMoney = null;
        }
        closeDataBase();
        return userMoney;
    }

    public int zcDataBase(int userid, double amount) throws SQLException {
        conDataBase();
        Double a=0.;

        String sql = "INSERT into `t_trade` (`id`,`out`,`in`)  VALUES (?,?,?)";
        statement = connection.prepareStatement(sql);
        statement.setInt(1, userid);//设置第一个参数的值
        statement.setDouble(2, amount);//设置第二个参数的值
        statement.setDouble(3, a);//设置第三个参数的值
        int i;
        i = statement.executeUpdate();

        closeDataBase();
        return i;
    }

    public int srDataBase(int userid, double amount) throws SQLException {
        conDataBase();
        Double a=0.;

        String sql = "INSERT into `t_trade` (`id`,`out`,`in`)  VALUES (?,?,?)";
        statement = connection.prepareStatement(sql);
        statement.setInt(1, userid);//设置第一个参数的值
        statement.setDouble(2, a);//设置第二个参数的值
        statement.setDouble(3, amount);//设置第三个参数的值
        int i;
        i = statement.executeUpdate();

        closeDataBase();
        return i;
    }

}